package com.yjd.bi.etl.sql;

import com.yjd.bi.comm.utils.DbNameUtils;

public class PromotionSqlConstants {
	public final static String SPREADER_COUNT="SELECT count(1) FROM "+DbNameUtils.get("yjd_spreader")+" where addtime>? and addtime<? and spread_regist_count>0";
	public final static String SPREADER_USER_COUNT="SELect count(1) from "+DbNameUtils.get("yjd_spread_relation")+" where addtime>? and addtime<?";
	public final static String SPREADER_RECHARGE_USER_COUNT="select count(1) from "+DbNameUtils.get("yjd_spread_relation")+" a where a.addtime  >=?   and  a.addtime<= ? and exists(select distinct(b.user_id) from "+DbNameUtils.get("yjd_order")+" b where status=1 and b.addtime  >=?   and  b.addtime<= ? and b.user_id=a.register);";
	public final static String SPREADER_INVEST_USER_COUNT="select count(1) from "+DbNameUtils.get("yjd_spread_relation")+" c where c.addtime  >=?  and  c.addtime<=? and exists( SELECT  distinct(ct.user_id) FROM  "+DbNameUtils.get("yjd_creditor_transfer")+" ct WHERE  oktime BETWEEN ? AND ?  AND `status` = 3 and c.register=ct.user_id ) and exists( SELECT distinct( i.user_id)  FROM   "+DbNameUtils.get("yjd_invest")+" i  WHERE addtime BETWEEN ? AND ? and i.user_id=c.register );";
	public final static String SPREADER_INVEST_AMOUNT="SELECT IFNULL(SUM(price), 0.00) FROM (SELECT  ct.price, ct.user_id FROM "+DbNameUtils.get("yjd_creditor_transfer")+" ct WHERE oktime BETWEEN ? AND ?  AND `status` = 3 UNION ALL SELECT i.price, i.user_id FROM "+DbNameUtils.get("yjd_invest")+" i  WHERE   addtime BETWEEN ? AND ? ) a WHERE a.user_id IN (SELECT  register  FROM "+DbNameUtils.get("yjd_spread_relation")+" b WHERE  b.addtime >= ?  AND b.addtime <= ?)";
	public final static String GET_MAX_STATISTIC_DATE = "select MAX(statistical_date) from "+DbNameUtils.get("yjd_spreader_statistics")+"  us";
	public final static String SPREADERSTATISTIC_INSERTSQL="INSERT INTO "+DbNameUtils.get("yjd_spreader_statistics")+" (`statistics_date`, `spreder_count`, `user_count`, `recharge_user_count`, `invest_user_count`, `user_invest_amount`,`create_time`) VALUES (?, ?, ?, ?, ?, ?,?)";
}
